package com.hnjowe.outputExcel;


import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import com.union.core.workModel.model.ModelInfo;
import com.union.core.workModel.service.ModelInfoService;

public class Basic {
	private ModelInfo modelInfo=null;
	private String sex;
	private ModelInfoService modelService;
	public Basic(){
		modelService=new ModelInfoService();
	}
	public HSSFWorkbook createExcel(String[] ids,String ipa){
		//定义单元格报头   
	    String worksheetTitle = "劳动模范（先进工作者）基本情况表";
	    HSSFWorkbook wb = new HSSFWorkbook();   
	    //==================================================================   
	    // 创建单元格样式    
	    HSSFCellStyle cellStyle = wb.createCellStyle();   
	    // 指定单元格居中对齐   
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
	    // 指定单元格垂直居中对齐   
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
	    //设边框
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    // 指定当单元格内容显示不下时自动换行   
	    cellStyle.setWrapText(true);   
	    //------------------------------------------------------------------   
	    // 设置单元格字体   
	    HSSFFont font = wb.createFont(); 
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	    font.setFontName("宋体");   
	    font.setFontHeight((short) 250);   
	    cellStyle.setFont(font);   
	    if(null!=ids){
	    	for(String modelId:ids){
	    		//根据id查询劳模基本信息
	    		if(!"".equals(modelId)){
	    		int id=Integer.parseInt(modelId);
	    		modelInfo=modelService.queryModelById(id);
	    		sex=modelInfo.getModel_sex()==2?"女":"男";
	        //工作表名   
	        String worksheet = "劳模"+modelInfo.getModel_name()+"信息";   
	           
	        HSSFSheet sheet = wb.createSheet(worksheet);
	        //列宽
	        sheet.setColumnWidth((short)0, (short)2800);
	        sheet.setColumnWidth((short)1, (short)2800);
	        sheet.setColumnWidth((short)2, (short)2800);
	        sheet.setColumnWidth((short)3, (short)2800);
	        sheet.setColumnWidth((short)4, (short)2800);
	        sheet.setColumnWidth((short)5, (short)2800);
	        sheet.setColumnWidth((short)6, (short)2400);
	        sheet.setColumnWidth((short)7, (short)2800);
	        sheet.setColumnWidth((short)8, (short)4000);
            //图片
            ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
            String ppa=modelInfo.getModel_image();
            try{
            BufferedImage bufferImg=ImageIO.read(new File(ipa+ppa));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,1023,255,(short)8,1,(short)8,4);//7列5行开始
            //插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
               
            }catch(IOException e){
            	e.printStackTrace();
            	
            }
	        ExportExcel exportExcel = new ExportExcel(wb, sheet);   

	        // 创建报表头部   
	        exportExcel.createNormalHead(worksheetTitle, 8);   
	        HSSFRow row = null;   
	        HSSFCell cell = null;   
//	        for(int i=0; i<list.size(); i++){  
	      //定义第一行开始   
	            row = sheet.createRow(1); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("姓名"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_name()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("性别"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(sex));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("出生年月"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_birth()));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("年龄"));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(Integer.toString(modelInfo.getAge())));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("相片"));
	            //定义第一行结束   
	            //定义第二行开始   
	            row = sheet.createRow(2); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("民族"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_nation()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("籍贯"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_native_place()));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("政治面貌"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_political_status()));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("学历"));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_education()));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第二行结束   
	            //定义第三行开始   
	            sheet.addMergedRegion(new Region(3, (short) 1, 3, (short) 5));
	            row = sheet.createRow(3); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("工作单位"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_work_unit()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("现状"));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_current()));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第三行结束   
	          //定义第四行开始   
	            sheet.addMergedRegion(new Region(4, (short) 5, 4, (short) 7));
	            sheet.addMergedRegion(new Region(1, (short) 8, 4, (short) 8));
	            row = sheet.createRow(4); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("职务"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_job()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("职称"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_post()));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("健康状况"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_health()));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第四行结束   
	            //定义第⑤行开始   
	            sheet.addMergedRegion(new Region(5, (short) 5, 5, (short) 8));
	            row = sheet.createRow(5); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("获奖年份"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_palm_year()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("劳模级别"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_level()));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("荣誉类别"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_honout_type()));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第⑤行结束   
	            //定义第六行开始   
	            sheet.addMergedRegion(new Region(6, (short) 0, 6, (short) 1));
	            sheet.addMergedRegion(new Region(6, (short) 2, 6, (short) 8));
	            row = sheet.createRow(6); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("家庭住址/邮递详址"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_address()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第六行结束  
	            //定义第七行开始   
	            sheet.addMergedRegion(new Region(7, (short) 0, 7, (short) 1));
	            sheet.addMergedRegion(new Region(7, (short) 2, 7, (short) 4));
	            sheet.addMergedRegion(new Region(7, (short) 6, 7, (short) 8));
	            row = sheet.createRow(7); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("户口所在地"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_native_place()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("所属片区"));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_area()));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第七行结束  
	            //定义第八行开始   
	            sheet.addMergedRegion(new Region(8, (short) 3, 8, (short) 4));
	            sheet.addMergedRegion(new Region(8, (short) 6, 7, (short) 8));
	            row = sheet.createRow(8); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("固定电话"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_phone()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("手机号码"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_cellphone()));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("电子邮箱"));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_email()));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第八行结束
	            //定义第九行开始   
	            sheet.addMergedRegion(new Region(9, (short) 1, 9, (short) 8));
	            row = sheet.createRow(9); 
	            row.setHeight((short)2800);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("个人简历"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_resume()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第九行结束  
	            //定义第十行开始   
	            sheet.addMergedRegion(new Region(10, (short) 1, 10, (short) 8));
	            row = sheet.createRow(10); 
	            row.setHeight((short)2800);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("主要事迹"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_deed()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第十行结束 
	            //定义第十一行开始   
	            sheet.addMergedRegion(new Region(11, (short) 1, 11, (short) 8));
	            row = sheet.createRow(11); 
	            row.setHeight((short)1500);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("备注"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(modelInfo.getModel_other()));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第十一行结束  
//	        }   
	    //}   
	            HSSFPrintSetup printSetup = sheet.getPrintSetup();
	            printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
	            printSetup.setScale((short)90);
	            sheet.setDisplayGridlines(false);
	            sheet.setPrintGridlines(false);
	            sheet.setMargin(HSSFSheet.TopMargin,( double ) 1.0 ); // 上边距
	            sheet.setMargin(HSSFSheet.BottomMargin,( double ) 1.0 ); // 下边距
	            sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.6 ); // 左边距
	            sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.6 ); // 右边距
	     }
	    	}
	    }else{
	        //工作表名   
	        String worksheet = "劳模信息表";   
	           
	        HSSFSheet sheet = wb.createSheet(worksheet);
	        //列宽
	        sheet.setColumnWidth((short)0, (short)2800);
	        sheet.setColumnWidth((short)1, (short)2800);
	        sheet.setColumnWidth((short)2, (short)2800);
	        sheet.setColumnWidth((short)3, (short)2800);
	        sheet.setColumnWidth((short)4, (short)2800);
	        sheet.setColumnWidth((short)5, (short)2800);
	        sheet.setColumnWidth((short)6, (short)2400);
	        sheet.setColumnWidth((short)7, (short)2800);
	        sheet.setColumnWidth((short)8, (short)4000);
//	      //图片
//	        String sDirPath = this.getServletConfig().getServletContext().getRealPath("/");
//	        System.out.println(sDirPath);
//	        ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
//	        BufferedImage bufferImg=ImageIO.read(new File(sDirPath+"/images/js.png"));
//	        ImageIO.write(bufferImg, "png", byteArrayOut);
//	        HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//	        HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,255,255,(short)7,5,(short)9,7);//7列5行开始
	        //插入图片
//	        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
//	                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
	        ExportExcel exportExcel = new ExportExcel(wb, sheet);   

	        // 创建报表头部   
	        exportExcel.createNormalHead(worksheetTitle, 8);   
	        HSSFRow row = null;   
	        HSSFCell cell = null;   
//	        for(int i=0; i<list.size(); i++){  
	      //定义第一行开始   
	            row = sheet.createRow(1); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("姓名"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("性别"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("出生年月"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("年龄"));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("相片"));
	            //定义第一行结束   
	            //定义第二行开始   
	            row = sheet.createRow(2); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("民族"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("籍贯"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("政治面貌"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("学历"));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第二行结束   
	            //定义第三行开始   
	            sheet.addMergedRegion(new Region(3, (short) 1, 3, (short) 5));
	            row = sheet.createRow(3); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("工作单位"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("现状"));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第三行结束   
	          //定义第四行开始   
	            sheet.addMergedRegion(new Region(4, (short) 5, 4, (short) 7));
	            sheet.addMergedRegion(new Region(1, (short) 8, 4, (short) 8));
	            row = sheet.createRow(4); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("职务"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("职称"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("健康状况"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第四行结束   
	            //定义第⑤行开始   
	            sheet.addMergedRegion(new Region(5, (short) 5, 5, (short) 8));
	            row = sheet.createRow(5); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("获奖年份"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("劳模级别"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("荣誉类别"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第⑤行结束   
	            //定义第六行开始   
	            sheet.addMergedRegion(new Region(6, (short) 0, 6, (short) 1));
	            sheet.addMergedRegion(new Region(6, (short) 2, 6, (short) 8));
	            row = sheet.createRow(6); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("家庭住址/邮递详址"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第六行结束  
	            //定义第七行开始   
	            sheet.addMergedRegion(new Region(7, (short) 0, 7, (short) 1));
	            sheet.addMergedRegion(new Region(7, (short) 2, 7, (short) 4));
	            sheet.addMergedRegion(new Region(7, (short) 6, 7, (short) 8));
	            row = sheet.createRow(7); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("户口所在地"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("所属片区"));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第七行结束  
	            //定义第八行开始   
	            sheet.addMergedRegion(new Region(8, (short) 3, 8, (short) 4));
	            sheet.addMergedRegion(new Region(8, (short) 6, 7, (short) 8));
	            row = sheet.createRow(8); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("固定电话"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("手机号码"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("电子邮箱"));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第八行结束
	            //定义第九行开始   
	            sheet.addMergedRegion(new Region(9, (short) 1, 9, (short) 8));
	            row = sheet.createRow(9); 
	            row.setHeight((short)2800);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("个人简历"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第九行结束  
	            //定义第十行开始   
	            sheet.addMergedRegion(new Region(10, (short) 1, 10, (short) 8));
	            row = sheet.createRow(10); 
	            row.setHeight((short)2800);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("主要事迹"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第十行结束 
	            //定义第十一行开始   
	            sheet.addMergedRegion(new Region(11, (short) 1, 11, (short) 8));
	            row = sheet.createRow(11); 
	            row.setHeight((short)1500);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("备注"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)7);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)8);  
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));
	            //定义第十一行结束  
//	        }   
	    //}   
	            HSSFPrintSetup printSetup = sheet.getPrintSetup();
	            printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
	            printSetup.setScale((short)90);
	            sheet.setDisplayGridlines(false);
	            sheet.setPrintGridlines(false);
	            sheet.setMargin(HSSFSheet.TopMargin,( double ) 1.0 ); // 上边距
	            sheet.setMargin(HSSFSheet.BottomMargin,( double ) 1.0 ); // 下边距
	            sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.6 ); // 左边距
	            sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.6 ); // 右边距
	    }
	     return wb;
	}
}
